package org.alex.sample.dao.Impl;

import java.util.Iterator;
import java.util.List;

import org.alex.sample.VO.ExamScoreVO;
import org.alex.sample.dao.ExamScoreDAO;
import org.alex.sample.model.ExamScore;
import org.alex.sample.model.Subject;
import org.alex.sample.page.PagerModel;
import org.alex.sample.page.SystemContext;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class ExamScoreDAOImpl extends BaseHibernateDAO<ExamScore> implements ExamScoreDAO {

	//管理成绩列表
	@Override
	public PagerModel<ExamScore> findAllExamScore() throws Exception {
		PagerModel<ExamScore> pager = new PagerModel<ExamScore>();
		int offset = SystemContext.getOffset();
		int pageSize = SystemContext.getPageSize();
		@SuppressWarnings("unchecked")
		List<ExamScore> examScoreList = this.getSession().createQuery("from ExamScore")
				.setFirstResult(offset).setMaxResults(pageSize).list();
		pager.setDates(examScoreList);
		pager.setOffset(offset);
		pager.setPageSize(pageSize);
		long totalSize = (long)this.getSession().createQuery("select count(*) from ExamScore").uniqueResult();
		pager.setTotalSize(totalSize);
		return pager;
	}
	
	//查找到所有科目名和id，科目搜索框下拉列表需要用到
	@Override
	public List<Subject> findSubjectNameAndId() throws Exception{
		@SuppressWarnings("unchecked")
		List<Subject> subjectList = this.createSqlQuery("select sub_id as subId,name from exam_subject")
				.setResultTransformer(new AliasToBeanResultTransformer(Subject.class)).list();
		return subjectList;
	}
	
	//根据科目名传的id、用户名（学号）、真实姓名、考试时间四个不同条件进行查找
	@Override
	public List<ExamScoreVO> searchExamScore(Integer subId, String userName, String name, String examTime) 
			throws Exception{
		//select exam_score.id as scoreId,exam_score.user_id as userId,exam_score.sub_id as subId,exam_score.exam_time as examTime,exam_score.score,
		//exam_user.user_name as userName,exam_user.name as name,
		//exam_subject.name as subjectName 
		//from exam_score,exam_user,exam_subject where exam_user.user_name='student3' and exam_user.name='学生3号' 
		//and exam_score.exam_time='2018-06-05' and exam_score.sub_id=6 and exam_score.sub_id=exam_subject.sub_id and exam_score.user_id=exam_user.id;
		
		String sql = "select exam_score.id as scoreId,exam_score.user_id as userId,exam_score.sub_id as subId,exam_score.exam_time as examTime,exam_score.score,"
				+ "exam_user.user_name as userName,exam_user.name as name, exam_subject.name as subjectName from exam_score,exam_user,exam_subject where ";
		//拼接用户名不为空或真实姓名不为空或两者都不为空或两者都为空的情况（4种情况,查出用户名和真实姓名等这两参数的用户的id）
		if(userName != "" && userName != null) {
			sql += "exam_user.user_name='" + userName + "'";
		}
		if(name != "" && name != null) {
			if(userName != "" && userName != null) {
				sql += " and ";
			}
			sql += "exam_user.name='" + name + "'";
		}
		if(subId != null && subId > 0) {
			if((userName != "" && userName != null) || (name != "" && name != null)) {
				sql += " and ";
			}
			sql += "exam_score.sub_id=" + subId;
		}
		if(examTime != "" && examTime != null) {
			if((userName != "" && userName != null) || (name != "" && name != null) || (subId != null && subId > 0)) {
				sql += " and ";
			}
			sql += "exam_score.exam_time='" + examTime + "'";
		}
		sql += " and exam_score.sub_id=exam_subject.sub_id and exam_score.user_id=exam_user.id";
		@SuppressWarnings("unchecked")
		List<ExamScoreVO> examScoreVOList = this.createSqlQuery(String.valueOf(sql))
				.addScalar("score", StandardBasicTypes.DOUBLE)
				.addScalar("scoreId", StandardBasicTypes.INTEGER)
				.addScalar("examTime", StandardBasicTypes.DATE)
				.addScalar("userId", StandardBasicTypes.INTEGER)
				.addScalar("subId", StandardBasicTypes.INTEGER)
				.addScalar("userName", StandardBasicTypes.STRING)
				.addScalar("name", StandardBasicTypes.STRING)
				.addScalar("subjectName", StandardBasicTypes.STRING)
				.setResultTransformer(new AliasToBeanResultTransformer(ExamScoreVO.class)).list();
		
		return examScoreVOList;
	}
	
	@Override
	public Iterator<ExamScore> iterate(String queryString, Object[] values) {
		return null;
	}
}
